﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.Win32;
using System.Windows.Forms;

namespace VOEISHubPushServiceClient
{
    class SQLCommunicator : ISQLCommunicator
    {
        private string _ServiceSQLServer = "YourSQLInstance";
        private string _ServiceDatabase = "YourSQLDatabase";
        private string _ServiceAuthentication = "Windows";
        private string _ServiceUserName = "";
        private string _ServicePassword = "";
        private string _ServiceConnectionString = "";
        private int _ServiceMaxRecords = 0;
        private int _ServiceDBTimeout = 300;

        public string ServiceSQLServer
        {
            get
            {
                return _ServiceSQLServer;
            }

            set
            {
                _ServiceSQLServer = value;                
            }
        }

        public string ServiceDatabase
        {
            get
            {
                return _ServiceDatabase;
            }

            set
            {
                _ServiceDatabase = value;
            }
        }

        public string ServiceAuthentication
        {
            get
            {
                return _ServiceAuthentication;
            }

            set
            {
                _ServiceAuthentication = value;
            }
        }

        public string ServiceUserName
        {
            get
            {
                return _ServiceUserName;
            }

            set
            {
                _ServiceUserName= value;
            }
        }

        public string ServicePassword
        {
            get
            {
                return _ServicePassword;
            }
            set
            {
                _ServicePassword = value;
            }
        }

        public string ServiceConnectionString
        {
            get
            {
                if (_ServiceAuthentication == "Windows")
                {
                    return "Data Source=" + _ServiceSQLServer + ";Initial Catalog=" + _ServiceDatabase + ";Integrated Security=SSPI;";
                }
                else
                {
                    return "Data Source=" + _ServiceSQLServer + ";Initial Catalog=" + _ServiceDatabase + ";User Id=" + _ServiceUserName + ";Password=**********;";
                }
            }
        }

        public int ServiceMaxRecords
        {
            get
            {
                return _ServiceMaxRecords;
            }
            set
            {
                _ServiceMaxRecords = value;
            }
        }

        public int ServiceDBTimeout
        {
            get
            {
                return _ServiceDBTimeout;
            }

            set 
            {
                _ServiceDBTimeout = value;
            }
        }

        public SQLCommunicator()
        {
            ServiceConfigurationManager svc = new ServiceConfigurationManager();
            Dictionary<string, string> DBDictionary = new Dictionary<string, string>();
            DBDictionary = svc.ReadDatabaseSettings();

            _ServiceSQLServer = DBDictionary["ServiceSQLServer"];
            _ServiceDatabase = DBDictionary["ServiceDatabase"];
            _ServiceAuthentication = DBDictionary["ServiceAuthentication"];
            _ServiceUserName = DBDictionary["ServiceUserName"];
            _ServicePassword = DBDictionary["ServicePassword"];
            _ServiceMaxRecords = Convert.ToInt32(DBDictionary["ServiceMaxRecords"]);
            _ServiceDBTimeout = Convert.ToInt32(DBDictionary["DBTimeout"]);
            SetSqlConnectionString();      
        }

        public SQLCommunicator(string _ServiceSQLServer, string ServiceDatabase)
        {
            _ServiceSQLServer = ServiceSQLServer;
            _ServiceDatabase = ServiceDatabase;
            _ServiceAuthentication = "Windows";
            SetSqlConnectionString();
        }

        public SQLCommunicator(string _ServiceSQLServer, string ServiceDatabase, string ServiceUserName, string ServicePassword)
        {
            _ServiceSQLServer = ServiceSQLServer;
            _ServiceDatabase = ServiceDatabase;
            _ServiceAuthentication = "SQL";
            _ServiceUserName = ServiceUserName;
            _ServicePassword = ServicePassword;
            SetSqlConnectionString();
        }        

        public void LoadDatabaseSettings(string TemplatePath)
        {
            ServiceConfigurationManager scm = new ServiceConfigurationManager();
            
            Dictionary<string,string> DBDictionary = new Dictionary<string,string>();
            scm.ApplicationConfigPath = TemplatePath;
            DBDictionary = scm.ReadDatabaseSettings();

            _ServiceSQLServer = DBDictionary["ServiceSQLServer"];
            _ServiceDatabase = DBDictionary["ServiceDatabase"];
            _ServiceAuthentication = DBDictionary["ServiceAuthentication"];
            _ServiceUserName = DBDictionary["ServiceUserName"];
            _ServicePassword = DBDictionary["ServicePassword"];
            _ServiceMaxRecords = Convert.ToInt32(DBDictionary["ServiceMaxRecords"]);
            _ServiceDBTimeout = Convert.ToInt32(DBDictionary["DBTimeout"]);
            SetSqlConnectionString();
        }

        public void SetSqlConnectionString()
        {
            //TODO: I need to make sure that none of the fields are null
            if (_ServiceAuthentication == "Windows")
            {
                _ServiceConnectionString = "Data Source=" + _ServiceSQLServer + ";Initial Catalog=" + _ServiceDatabase + ";Integrated Security=SSPI;";
            }
            else
            {
                _ServiceConnectionString = "Data Source=" + _ServiceSQLServer + ";Initial Catalog=" + _ServiceDatabase + ";User Id=" + _ServiceUserName + ";Password=" + _ServicePassword + ";";
            }
        }

        public System.Data.DataTable ListSqlInstances()
        {
            // Retrieve the enumerator instance and then the data.
            SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
            DataTable table = instance.GetDataSources();

            return table;
        }

        public System.Data.DataTable ListSQLDatabases()
        {
            //Retrieve the enumerator instance and then the data
            DataTable table = new DataTable();

            try
            {
                using (SqlConnection conn = new SqlConnection(_ServiceConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT name FROM sys.databases", conn))
                    {
                        conn.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        table.Load(reader);
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString());
            }

            return table;
        }

        public String GetPushResults(Int32 SyncID)
        {
            DataTable table = new DataTable();
            String pushResults = "";

            try
            {
                using (SqlConnection conn = new SqlConnection(_ServiceConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("EXEC dbo.usp_GetPushResults " + SyncID, conn))
                    {

                        conn.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            pushResults = reader[0].ToString();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString(), "Failed to retrieve push results.", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }

            return pushResults;
        }

        public Dictionary<string, string> ListDataSets()
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>();
            try
            {
                using (SqlConnection conn = new SqlConnection(_ServiceConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("EXEC dbo.usp_GetDataSets", conn))
                    {
                        conn.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            dictionary.Add(reader[0].ToString(), reader[1].ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString(), "Dataset Enumeration Failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }

            return dictionary;
        }

        public System.Collections.ArrayList ListProjectIDs()
        {
            ArrayList projectIDs = new ArrayList();

            try
            {
                using (SqlConnection conn = new SqlConnection(_ServiceConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("EXEC dbo.usp_GetProjectIDs", conn))
                    {
                        conn.Open();
                        SqlDataReader reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            projectIDs.Add(reader[0].ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString(), "Project ID Enumeration Failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }

            return projectIDs;
        }

        public Boolean TestConnection(string connString = "")
        {
            Boolean result = false;

            if (connString.Length <= 0)
            {
                connString = _ServiceConnectionString;
            }
          
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT 1 WHERE 1 = 1", conn))
                    {
                        conn.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        reader.Close();
                        result = true;
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString(), "Connection Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                result = false;
            }

            return result;
        }

        private void SetServiceConfigPath()
        {
            try
            {
                string strPath = "";
                RegistryKey services = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SYSTEM\CurrentControlSet\Services");
                if (services != null)
                {
                    strPath = "";
                    strPath = services.OpenSubKey("VOEIS Hub Push Service").GetValue("ImagePath").ToString();
                    strPath = strPath.Replace("\"", "");
                    strPath = strPath.Replace(".exe", ".exe.config");
                    Properties.Settings.Default.VOEISHubPushServicePath = strPath;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("VOEIS Hub Push Service Not Found", "Service Missing - application will now close.", MessageBoxButtons.OK, MessageBoxIcon.Error);
                MessageBox.Show(ex.Message.ToString(), "Error");
                System.Environment.Exit(99);
            }
        }
    }

}
